❮

doc: mentioned CREATE+ATTACH PARTITION as an alternative to CREATE TABLE..PARTITION OF

  • Jump to comment-1
    pryzby@telsasoft.com2022-07-18T14:33:04+00:00
    It's easy to use CREATE TABLE..LIKE + ALTER..ATTACH PARTITION to avoid acquiring a strong lock when creating a new partition. But it's also easy to forget. commit 76c0d1198cf2908423b321cd3340d296cb668c8e Author: Justin Pryzby <pryzbyj@telsasoft.com> Date: Mon Jul 18 09:24:55 2022 -0500 doc: mention CREATE+ATTACH PARTITION as an alternative to CREATE TABLE..PARTITION OF See also: 898e5e3290a72d288923260143930fb32036c00c Should backpatch to v12 diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 6bbf15ed1a4..db7d8710bae 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -619,6 +619,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM with <literal>DROP TABLE</literal> requires taking an <literal>ACCESS EXCLUSIVE</literal> lock on the parent table. </para> + + <para> + Note that creating a partition acquires an <literal>ACCESS + EXCLUSIVE</literal> lock on the parent table. + It may be preferable to first CREATE a separate table and then ATTACH it, + which does not require as strong of a lock. + See <link linkend="sql-altertable-attach-partition">ATTACH PARTITION</link> + and <xref linkend="ddl-partitioning"/> for more information. + </para> + </listitem> </varlistentry>
    • Jump to comment-1
      andrew@dunslane.net2022-07-18T14:39:09+00:00
      On 2022-07-18 Mo 10:33, Justin Pryzby wrote: > It's easy to use CREATE TABLE..LIKE + ALTER..ATTACH PARTITION to avoid > acquiring a strong lock when creating a new partition. > But it's also easy to forget. > > commit 76c0d1198cf2908423b321cd3340d296cb668c8e > Author: Justin Pryzby <pryzbyj@telsasoft.com> > Date: Mon Jul 18 09:24:55 2022 -0500 > > doc: mention CREATE+ATTACH PARTITION as an alternative to CREATE TABLE..PARTITION OF > > See also: 898e5e3290a72d288923260143930fb32036c00c > Should backpatch to v12 > > diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml > index 6bbf15ed1a4..db7d8710bae 100644 > --- a/doc/src/sgml/ref/create_table.sgml > +++ b/doc/src/sgml/ref/create_table.sgml > @@ -619,6 +619,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM > with <literal>DROP TABLE</literal> requires taking an <literal>ACCESS > EXCLUSIVE</literal> lock on the parent table. > </para> > + > + <para> > + Note that creating a partition acquires an <literal>ACCESS > + EXCLUSIVE</literal> lock on the parent table. > + It may be preferable to first CREATE a separate table and then ATTACH it, > + which does not require as strong of a lock. > + See <link linkend="sql-altertable-attach-partition">ATTACH PARTITION</link> > + and <xref linkend="ddl-partitioning"/> for more information. > + </para> > + > </listitem> > </varlistentry> > Style nitpick. I would prefer "does not require as strong a lock." cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
      • Jump to comment-1
        rob@xzilla.net2022-08-04T05:45:49+00:00
        On Mon, Jul 18, 2022 at 10:39 AM Andrew Dunstan <andrew@dunslane.net> wrote: > On 2022-07-18 Mo 10:33, Justin Pryzby wrote: > > It's easy to use CREATE TABLE..LIKE + ALTER..ATTACH PARTITION to avoid > > acquiring a strong lock when creating a new partition. > > But it's also easy to forget. > > > > commit 76c0d1198cf2908423b321cd3340d296cb668c8e > > Author: Justin Pryzby <pryzbyj@telsasoft.com> > > Date: Mon Jul 18 09:24:55 2022 -0500 > > > > doc: mention CREATE+ATTACH PARTITION as an alternative to CREATE TABLE..PARTITION OF > > > > See also: 898e5e3290a72d288923260143930fb32036c00c > > Should backpatch to v12 > > > > diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml > > index 6bbf15ed1a4..db7d8710bae 100644 > > --- a/doc/src/sgml/ref/create_table.sgml > > +++ b/doc/src/sgml/ref/create_table.sgml > > @@ -619,6 +619,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM > > with <literal>DROP TABLE</literal> requires taking an <literal>ACCESS > > EXCLUSIVE</literal> lock on the parent table. > > </para> > > + > > + <para> > > + Note that creating a partition acquires an <literal>ACCESS > > + EXCLUSIVE</literal> lock on the parent table. > > + It may be preferable to first CREATE a separate table and then ATTACH it, > > + which does not require as strong of a lock. > > + See <link linkend="sql-altertable-attach-partition">ATTACH PARTITION</link> > > + and <xref linkend="ddl-partitioning"/> for more information. > > + </para> > > + > > </listitem> > > </varlistentry> > > > > Style nitpick. > > I would prefer "does not require as strong a lock." > FWIW, this is also proper grammar as well. After reading this again, it isn't clear to me that this advice would be more appropriately placed into Section 5.11, aka https://www.postgresql.org/docs/current/ddl-partitioning.html, but in lieu of a specific suggestion for where to place it there (I haven't settled on one yet), IMHO, I think the first sentence of the suggested change should be rewritten as: <para> Note that creating a partition using <literal>PARTITION OF<literal> requires taking an <literal>ACCESS EXCLUSIVE</literal> lock on the parent table. It may be preferable to first CREATE a separate table... Robert Treat https://xzilla.net